﻿using Microsoft.Data.SqlClient;
using Model;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dapper;
using Microsoft.EntityFrameworkCore;

namespace DAL
{
    public class WhOutboundDetailDal
    {
        private readonly AppDbContext _dbContext;
        private string URL;

        public WhOutboundDetailDal(AppDbContext dbContext)
        {
            dynamic jsonObject = JsonConvert.DeserializeObject(File.ReadAllText("appsettings.json"));
            URL = jsonObject.ConnectionStrings.SqlServer;
            _dbContext = dbContext;
        }

        /// <summary>
        /// 根据出库单ID获取明细项
        /// </summary>
        /// <param name="stockOutOrderId">出库单ID</param>
        /// <returns></returns>
        public async Task<List<StockOutItemDto>> GetDetailsByOrderId(long stockOutOrderId)
        {
            using (SqlConnection con = new SqlConnection(URL))
            {
                string sql = @"
                SELECT 
                    p.goods_name AS GoodsName,
                    wod.out_stock_quantity AS OutStockQuantity,
                    wod.out_stock_unit_price AS OutStockUnitPrice,
                    (wod.out_stock_quantity * wod.out_stock_unit_price) AS OutStockAmount,
                    wod.batch_no AS BatchNo,
                    wod.production_date AS ProductionDate,
                    wod.remarks AS Remarks
                FROM [dbo].[wh_outbound_detail] wod
                LEFT JOIN [dbo].[bi_product] p ON wod.goods_code = p.goods_code
                WHERE wod.stock_out_order_Id = @StockOutOrderId
                ORDER BY wod.stock_out_detail_id";

                return (await con.QueryAsync<StockOutItemDto>(sql, new { StockOutOrderId = stockOutOrderId })).ToList();
            }
        }
        /// <summary>
        /// 新增
        /// </summary>
        /// <param name="data">实体</param>
        /// <returns>结果</returns>
        public async Task<int> CreateAsync(WhOutboundDetail data)
        {
            // 新增实体到数据库集
            _dbContext.WhOutboundDetail.Add(data);
            // 保存更改
            return await _dbContext.SaveChangesAsync();
        }

        /// <summary>
        /// 根据ID查询
        /// </summary>
        /// <param name="Id">ID</param>
        /// <returns>用户列表</returns>
        public async Task<WhOutboundDetail> GetByIdAsync(long Id)
        {
            // 根据ID查询
            return await _dbContext.WhOutboundDetail.FirstOrDefaultAsync(g => g.StockOutDetailId == Id);
        }
    }
}